import { Pool, PoolConfig } from 'pg';
import { config } from './index';

// PostgreSQL连接池配置
const poolConfig: PoolConfig = {
  host: config.database.host,
  port: config.database.port,
  database: config.database.name,
  user: config.database.username,
  password: config.database.password,
  ssl: config.database.ssl ? { rejectUnauthorized: false } : false,
  
  // 连接池配置
  min: config.database.pool.min,
  max: config.database.pool.max,
  idleTimeoutMillis: config.database.pool.idleTimeoutMillis,
  connectionTimeoutMillis: config.database.pool.acquireTimeoutMillis,
  
  // 查询超时
  query_timeout: 30000,
  statement_timeout: 60000,
  
  // 连接配置
  application_name: 'dw-sql-script-manager',
};

// 创建连接池
export const pool = new Pool(poolConfig);

// 连接池事件监听
pool.on('connect', (_client) => {
  console.log('New PostgreSQL client connected');
});

pool.on('error', (err, _client) => {
  console.error('Unexpected error on idle PostgreSQL client', err);
  process.exit(-1);
});

pool.on('acquire', (_client) => {
  console.log('PostgreSQL client acquired from pool');
});

pool.on('release', (_client) => {
  console.log('PostgreSQL client released back to pool');
});

// 数据库连接测试
export const testConnection = async (): Promise<boolean> => {
  try {
    const client = await pool.connect();
    const result = await client.query('SELECT NOW() as current_time');
    client.release();
    console.log('Database connection test successful:', result.rows[0]);
    return true;
  } catch (error) {
    console.error('Database connection test failed:', error);
    return false;
  }
};

// 优雅关闭连接池
export const closePool = async (): Promise<void> => {
  try {
    await pool.end();
    console.log('PostgreSQL connection pool closed');
  } catch (error) {
    console.error('Error closing PostgreSQL connection pool:', error);
  }
};

// 执行查询的辅助函数
export const query = async (text: string, params?: any[]): Promise<any> => {
  const start = Date.now();
  try {
    const result = await pool.query(text, params);
    const duration = Date.now() - start;
    console.log('Executed query', { text, duration, rows: result.rowCount });
    return result;
  } catch (error) {
    console.error('Query error', { text, error });
    throw error;
  }
};

// 事务执行辅助函数
export const transaction = async (callback: (client: any) => Promise<any>): Promise<any> => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
};

export default pool;